﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using CustomErp;
using RkLibs.DbOperate;
namespace CustomErp.Database
{
    /// <summary>
    /// 数据库管理类
    /// </summary>
    public class DbMgr
    {
        private static DbMgr UniDbMgr = null;
        /// <summary>
        /// 数据库文件名称
        /// </summary>
        private const string DB_FILE_NAME = "db.accdb";
        /// <summary>
        /// 数据库表名
        /// </summary>
        private const string DB_TABLE_NAME = "MainSht";
        /// <summary>
        /// 数据库列-姓名
        /// </summary>
        private const string DB_COL_NAME = "Persons";
        /// <summary>
        /// 数据库列-生日
        /// </summary>
        private const string DB_COL_BIRTH = "Birthday";
        /// <summary>
        /// 数据库列-阴阳历
        /// </summary>
        private const string DB_COL_SOLAR = "SolarLunar";
        /// <summary>
        /// 数据库列-联系方式
        /// </summary>
        private const string DB_COL_PHONE = "Phone";

        private string[] DB_COLS = new string[] { DB_COL_NAME, DB_COL_BIRTH, DB_COL_SOLAR, DB_COL_PHONE};
        private string[] DB_COLS_TYPE = new string[] { "Text", "Text", "Yes/No", "Text", "Text" };
        /// <summary>
        /// 数据库类
        /// </summary>
        DatabaseMgr database;
        /// <summary>
        /// 构造函数
        /// </summary>
        private DbMgr()
        {
            //默认使用access数据库
            string dbFilePath = gValue.SoftwareDataDir + DB_FILE_NAME;
            InitDbFile(dbFilePath);
            database = new DatabaseMgr(DatabaseFileType.Access, dbFilePath);
            //for (int idx = 0; idx < DB_COLS.Length; idx++)
            //{
            //    if (!ColumnIsExist(DB_COLS[idx]))
            //    {
            //        AddColumn(DB_COLS[idx], DB_COLS_TYPE[idx]);
            //    }
            //}
        }

        public void CloseDatabase()
        {
            database.CloseDatabase();
        }
        public static bool DatabaseFileExsit()
        {
            string filePath = gValue.SoftwareDataDir + DB_FILE_NAME;
            return File.Exists(filePath);
        }
        /// <summary>
        /// 获取数据库管理类
        /// </summary>
        /// <returns></returns>
        public static DbMgr GetDbMgr()
        {
            if (UniDbMgr == null)
            {
                UniDbMgr = new DbMgr();
            }
            return UniDbMgr;
        }
        /// <summary>
        /// 返回所有姓名
        /// </summary>
        /// <returns></returns>
        public string[] GetAllNames()
        {
            string cmd = string.Format("SELECT {0} FROM {1} ORDER BY {2} ASC ",DB_COL_NAME,DB_TABLE_NAME,DB_COL_BIRTH);// "SELECT Persons,Birthday FROM " + DB_TABLE_NAME + " ORDER BY Birthday ASC";
            DataTable dt = database.ExecuteReader(cmd);
            if (dt.Rows.Count>0)
            {
                string[] ArrRet = new string[dt.Rows.Count];
                for (int rowIdx = 0; rowIdx < dt.Rows.Count;rowIdx++ )
                {
                    ArrRet[rowIdx] = dt.Rows[rowIdx][0].ToString();
                }
                return ArrRet;
            }
            return null;
        }
        /// <summary>
        /// 添加姓名
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool AddName(string name)
        {
            if (NameIsExit(name))
            {
                return false;
            }
            string cmd = string.Format("INSERT INTO {0} ({1}) VALUES('{2}')", DB_TABLE_NAME, DB_COL_NAME, name);
            if (database.ExecuteNonQuery(cmd) ==0)
            {
                return false;
            }
            return true;
        }
        /// <summary>
        /// 判断姓名是否存在
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool NameIsExit(string name)
        {
            string cmd = string.Format("SELECT {1} FROM {0} WHERE {1}='{2}'", DB_TABLE_NAME, DB_COL_NAME, name);
            DataTable dt = database.ExecuteReader(cmd);
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 删除姓名
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool DeleteName(string name)
        {
            if (NameIsExit(name))
            {
                string cmd = string.Format("DELETE FROM {0} WHERE {1}='{2}'", DB_TABLE_NAME, DB_COL_NAME, name);
                int iRet = database.ExecuteNonQuery(cmd);
                if (iRet == 0)
                {
                    return false;
                }
            }
            return true;
        }
        //public bool UpdateRecord(PersonInfo info)
        //{
        //    if (NameIsExit(info.Name))
        //    {
        //        string cmd;
        //        cmd = string.Format("UPDATE {0} SET {1} = '{2}', {3} = {4}, {5} = '{6}' WHERE {7} = '{8}'",
        //            DB_TABLE_NAME,DB_COL_BIRTH,info.Birthday,DB_COL_SOLAR,info.IsLunar,DB_COL_PHONE,info.Phone,DB_COL_NAME,info.Name);
        //        int iRet = database.ExecuteNonQuery(cmd);
        //        if (iRet == 0)
        //        {
        //            return false;
        //        }
        //        return true;
        //    }
        //    else
        //    {
        //        return InsertRecord(info);
        //    }
        //}
        //public bool InsertRecord(PersonInfo info)
        //{
        //    string cmd;
        //    cmd = string.Format("INSERT INTO {0} ({1},{2},{3},{4}) VALUES ('{5}','{6}',{7},'{8}')",
        //        DB_TABLE_NAME,DB_COL_NAME,DB_COL_BIRTH,DB_COL_SOLAR,DB_COL_PHONE,
        //        info.Name,info.Birthday,info.IsLunar,info.Phone);
        //    int iRet = database.ExecuteNonQuery(cmd);
        //    if (iRet == -1)
        //    {
        //        return false;
        //    }
        //    return true;
        //}
        //public PersonInfo GetRecord(string name)
        //{
        //    PersonInfo info = new PersonInfo();
        //    string cmd = string.Format("SELECT {1},{2},{3},{4} FROM {0} WHERE {1} = '{5}'",
        //        DB_TABLE_NAME,DB_COL_NAME,DB_COL_BIRTH,DB_COL_SOLAR,DB_COL_PHONE,name);
        //    DataTable dt= database.ExecuteReader(cmd);
        //    if (dt.Rows.Count>0)
        //    {
        //        info.Name  = dt.Rows[0][0].ToString();
        //        info.IsLunar = Convert.ToBoolean(dt.Rows[0][2]);
        //        info.Phone = dt.Rows[0][3].ToString();
        //        //必须最后设置生日，因为要计算
        //        info.Birthday = dt.Rows[0][1].ToString();
        //    }
        //    return info;
        //}
        public bool DeleteAllRecord()
        {
            BackupDataBase();
            string cmd = "DELETE * FROM " + DB_TABLE_NAME;
            int iRet = database.ExecuteNonQuery(cmd);
            if (iRet == 0)
            {
                return false;
            }
            return true;
        }
        public bool ExportData(string filePath)
        {
            return true;
        }
        private void BackupDataBase()
        {
            try
            {
                int i = 0;
                string backPath;
                while (File.Exists(backPath = database.GetDatabaseFilePath() + ".back" + i++))
                {
                    ;
                }
                File.Copy(database.GetDatabaseFilePath(), backPath, true);
            }
            catch
            {
            	
            }
        }
        private void InitDbFile(string dbFilePath)
        {
            if (!File.Exists(dbFilePath))
            {
                File.Copy(DB_FILE_NAME, dbFilePath, true);
            }
        }


        private bool ColumnIsExist(string colName)
        {
            string cmd = string.Format("SELECT * FROM {0}",DB_TABLE_NAME,colName);
            DataTable dt = database.ExecuteReader(cmd);
            for (int idx = 0; idx < dt.Columns.Count; idx++)
            {
                if (dt.Columns[idx].ColumnName == colName)
                {
                    return true;
                }
            }
            return false;
        }
        private bool AddColumn(string colName, string dataType)
        {
            string cmd = string.Format("ALTER TABLE {0} ADD {1} {2}", DB_TABLE_NAME, colName, dataType);
            int iRet = database.ExecuteNonQuery(cmd);
            if (iRet != -1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        
    }
}
